//------------------------------------------------------------------------------
#include <QSqlQuery>
#include <QSqlError>
#include <QApplication>
#include <QSqlRecord>
#include <QClipboard>
#include "version.h"
#include "CMySQL.h"
#include <QtDebug>
//------------------------------------------------------------------------------
CMySQL *CMySQL::_instance=NULL;
//------------------------------------------------------------------------------
CMySQL * CMySQL::getInstance(void)
{		
	if(_instance == NULL)
	{	
		_instance=new CMySQL();
	}
	
	return _instance;
}
//------------------------------------------------------------------------------
bool CMySQL::dbConnect(QString host, QString user, QString passWord, QString base)
{
	bool ret;	
	
	db=QSqlDatabase::addDatabase("QMYSQL");
	db.setHostName(host);
	db.setDatabaseName(base);
	db.setUserName(user);
	db.setPassword(passWord);
	db.setConnectOptions("CLIENT_SSL=1;CLIENT_IGNORE_SPACE=1");
	
	if((ret=db.open()))
	{
		_query=new QSqlQuery(db);
	}
	
	return ret;
}
//------------------------------------------------------------------------------
bool CMySQL::execQuery(QString requete, bool showMessage, QVariant *lastInsertId)
{
	_query->clear();
	if(!_query->exec(requete))
	{
		if(showMessage)
		{	
			QString message=_query->lastError().text();
#ifdef COMEXPERT_DEBUG
			message+="\n"+requete;
#endif //COMEXPERT_DEBUG
		
			QMessageBox msgBox(	QMessageBox::Warning, QObject::tr("Erreur SQL"), message, QMessageBox::Ok);
			msgBox.exec();
	
#ifdef COMEXPERT_DEBUG
			QClipboard *clipboard=QApplication::clipboard();
	
			clipboard->setText(requete);
#endif //COMEXPERT_DEBUG
		}
		return false;
	}
	
	if(lastInsertId != 0)
	{
		*lastInsertId=_query->lastInsertId();
	}

	return true;
} 
//------------------------------------------------------------------------------
bool CMySQL::execQuery(QString requete, QSqlQuery **query, bool showMessage, QVariant *lastInsertId)
{
	*query=new QSqlQuery(db);
	if(!(*query)->exec(requete))
	{
		if(showMessage)
		{	
			QString message=(*query)->lastError().text();
#ifdef COMEXPERT_DEBUG
			message+="\n"+requete;
#endif //COMEXPERT_DEBUG
		
			QMessageBox msgBox(	QMessageBox::Warning, QObject::tr("Erreur SQL"), message, QMessageBox::Ok);
			msgBox.exec();
	
#ifdef COMEXPERT_DEBUG
			QClipboard *clipboard=QApplication::clipboard();
	
			clipboard->setText(requete);
#endif //COMEXPERT_DEBUG
		}
		return false;
	}
	
	if(lastInsertId != 0)
	{
		*lastInsertId=(*query)->lastInsertId();
	}

	return true;
} 
//------------------------------------------------------------------------------
bool CMySQL::execQuery(QSqlQuery *query, bool showMessage, QVariant *lastInsertId)
{
	if(!query->exec())
	{
		if(showMessage)
		{	
			QString message=query->lastError().text();
#ifdef COMEXPERT_DEBUG
			message+="\n"+query->executedQuery();
#endif //COMEXPERT_DEBUG
		
			QMessageBox msgBox(	QMessageBox::Warning, QObject::tr("Erreur SQL"), message, QMessageBox::Ok);
			msgBox.exec();
	
#ifdef COMEXPERT_DEBUG
			qDebug() << query->executedQuery();
#endif //COMEXPERT_DEBUG
		}
		return false;
	}
	
	if(lastInsertId != 0)
	{
		*lastInsertId=query->lastInsertId();
	}

	return true;
} 
//------------------------------------------------------------------------------
int CMySQL::getNbEnregistrement(QString tableName)
{
	QString requete="SELECT COUNT(*) FROM "+tableName;
	QSqlQuery *query;
	int ret=0;
	
	if(!execQuery(requete, &query))
	{
		delete query;
		return 0;
	}
	
	if(query->next())
	{
		ret=query->record().value(0).toInt();
	}
	
	delete query;
	
	return ret;
}
//------------------------------------------------------------------------------
QString CMySQL::getNextCode(QString tableName, QString champName, QString condition)
{
	QSqlQuery *query;
	QString requete="SELECT IFNULL(MAX("+champName+"), 0)+1 FROM "+tableName;
	QString ret="";
	
	if(condition != "")
	{
		requete+=" WHERE "+condition;
	}
	
	if(!execQuery(requete, &query))
	{
		delete query;
		return "";
	}
	
	if(query->next())
	{
		ret=query->record().value(0).toString();
	}
	
	delete query;
	
	return ret;
}
//------------------------------------------------------------------------------
bool CMySQL::existe(QString tableName, QString champName, QString value)
{
	QSqlQuery *query;
	QString requete="SELECT COUNT(*) FROM "+tableName+" WHERE "+champName+" = \""+value+"\" ";
	bool ret=false;
	
	if(!execQuery(requete, &query))
	{
		delete query;
		return false;
	}
	
	if(query->next())
	{
		ret=query->record().value(0).toInt() != 0;
	}
	
	delete query;
	
	return ret;
}
//------------------------------------------------------------------------------
bool CMySQL::canDelete(QString tableName, QString champName, QString value)
{
	if(existe(tableName, champName, value))
	{
		QMessageBox msgBox(	QMessageBox::Warning, QObject::tr("Attention"), 
							QObject::tr("Impossible de supprimer la fiche courante, elle est sert de source d'information (")+tableName+QObject::tr(")"), QMessageBox::Ok);
		msgBox.exec();
		
		return false;
	}
	return true;
}
//------------------------------------------------------------------------------
QVector<CMySQL::SChampRecherche> CMySQL::getChampsRecherche(QString tableName, bool includeTableName)
{
	QString requete;
	QSqlQuery *query;
	QVector<CMySQL::SChampRecherche> list=QVector<CMySQL::SChampRecherche> ();
	
	requete="SELECT schema.defaut, IFNULL(COLUMN_NAME, CONCAT(\"(\", calcul, \")\")) AS cName, ";
	requete+="IFNULL(COLUMN_COMMENT, champ) AS cComment ";
	requete+="FROM "+_base+".schema LEFT JOIN information_schema.COLUMNS ON `table`=TABLE_NAME AND champ=COLUMN_NAME ";
	requete+="WHERE `table`=\""+tableName+"\" AND (attribut & 2 <> 0  OR attribut & 8 <> 0) AND ";
	requete+="IF(NOT ISNULL(TABLE_SCHEMA), TABLE_SCHEMA=\""+_base+"\", 1) ";
	requete+="ORDER BY positionRecherche";
	
	if(execQuery(requete, &query))
	{
		while(query->next())
		{
			QSqlRecord rec=query->record();
			QString cName=rec.value("cName").toString();
			QString cComment=rec.value("cComment").toString();
			int def=rec.value("default").toInt();
			
			if(!cName.contains("%"))
			{
				cName="%1."+cName;
			}
			
			if(includeTableName)
			{
				cComment+=" ("+tableName+")";
			}
			cName=cName.arg(tableName);
		
			CMySQL::SChampRecherche champRecherche(cName, cComment, def);
			list.append(champRecherche);
		}
	}
	delete query;
	
	return list;
}
//------------------------------------------------------------------------------
QDate CMySQL::getCurrentDate(void)
{
	QDate d;
	QString requete;
	QSqlQuery *query;
	
	requete="SELECT CURDATE()";
	if(!execQuery(requete, &query))
	{
		delete query;
		return QDate();
	}
	
	if(query->next())
	{
		d=query->record().value(0).toDate();
	}
	
	delete query;

	return d;
}
//------------------------------------------------------------------------------
QTime CMySQL::getCurrentTime(void) {
	QTime t;
	QString requete;
	QSqlQuery *query;
	
	requete="SELECT CURTIME()";
	if(!execQuery(requete, &query)) {
		delete query;
		return QTime();
	}
	
	if(query->next()) {
		t=query->record().value(0).toTime();
	}
	
	delete query;

	return t;
}
//------------------------------------------------------------------------------
bool CMySQL::versionOk(QString &versionMinLogiciel, QString &versionBase) {
	QString requete;
	QSqlQuery *query;
	bool ret=false;
	
	requete="SELECT logiciel, base FROM version";
	if(execQuery(requete, &query)) {
		if(query->next()) {
			QSqlRecord rec=query->record();
			
			versionMinLogiciel=rec.value("logiciel").toString();
			versionBase=rec.value("base").toString();
			
			ret=(VERSION_LOGICIEL >= versionMinLogiciel && versionBase >= VERSION_MIN_BASE );
		}
	}
	
	delete query;
	
	return ret;
}
//------------------------------------------------------------------------------
void CMySQL::close(void) {
	db.close();
	delete _instance;
	_instance=NULL;
}
//------------------------------------------------------------------------------
QString CMySQL::makeDocName(QString code, QString prefix, QString champDate, QString tableName) {
	QString requete = "SELECT makeDocName('"+code+"', '"+prefix+"', "+champDate+") As docName ";
	requete+="FROM "+tableName;
	QSqlQuery *query;
	QString ret="";
	
	if(execQuery(requete, &query)) {
		if(query->next()) {
			QSqlRecord rec=query->record();
			
			ret=rec.value("docName").toString();
		}
	}
	
	delete query;
	
	return ret;
}
//------------------------------------------------------------------------------